package com.fpt.quanlysanbong.dao;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.fpt.quanlysanbong.domain.Account;
import com.fpt.quanlysanbong.domain.SoccerOrder;

public class SoccerOrderDao extends BaseDao {
	public boolean addSoccerOrder(List<SoccerOrder> soccerOrders) {
		boolean isSuccess = false;
		String sql1 = "Insert into SoccerOrder(personId, timeId, start_date, end_date, status, OrderNo, deposits) values (?,?,?,?,?,?,?)";

		try {
			conn.setAutoCommit(false);
			// DateFormat formatter = new SimpleDateFormat("yy/mm/dd");
			for (SoccerOrder soccerOrder : soccerOrders) {
				prst = conn.prepareStatement(sql1);
				prst.setInt(1, soccerOrder.getPerson().getId());
				prst.setInt(2, soccerOrder.getTimeDivision().getId());
				prst.setDate(3, new Date(soccerOrder.getStartDate().getTime()));
				prst.setDate(4, new Date(soccerOrder.getEndDate().getTime()));
				prst.setBoolean(5, true);
				prst.setString(6, soccerOrder.getOrderNo());
				prst.setFloat(7, soccerOrder.getDeposits());
				int affectedRows = prst.executeUpdate();
				if (affectedRows == 0) {
					throw new SQLException("Creating Soccer Order failed, no rows affected.");
				}
			}
			conn.commit();
			isSuccess = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return isSuccess;
	}

	public List<SoccerOrder> getListOrderByKeyWord(String keyword, int soccerFieldId) {
		if (StringUtils.isBlank(keyword)) {
			keyword = StringUtils.EMPTY;
		}

		List<SoccerOrder> lst = new ArrayList<SoccerOrder>();

		String sql = "Select so.* From dbo.SoccerOrder so inner join dbo.Person p on so.personId = p.id inner join dbo.TimeDivision td "
				+ " on so.timeId = td.id  WHERE (so.OrderNo like '%"
				+ keyword
				+ "%' OR p.name like '%"
				+ keyword
				+ "%') " + " and datediff(d, so.end_date,GETDATE()) <= 0 and so.status = 1";

		if (soccerFieldId != 0) {
			sql += " and td.soccerFieldId = " + soccerFieldId;
		}

		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				SoccerOrder so = new SoccerOrder();

				so.setId(rs.getInt("id"));
				so.setOrderNo(rs.getString("OrderNo"));
				so.setStatus(rs.getBoolean("status"));
				so.setStartDate(rs.getDate("start_date"));
				so.setEndDate(rs.getDate("end_date"));
				so.setTimeDivision(new SoccerFieldDao().getTimeDivisionById(rs.getInt("timeId")));
				so.setPerson(new PersonDao().getPersonById(rs.getInt("personId")));

				lst.add(so);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return lst;
	}

	public List<SoccerOrder> getListOrderByDate(String from, String to, int soccerFieldId) {
		List<SoccerOrder> lst = new ArrayList<SoccerOrder>();

		if (StringUtils.isBlank(from) || StringUtils.isBlank(to)) {
			return lst;
		}

		SimpleDateFormat f = new SimpleDateFormat("dd/MM/yyyy");

		try {
			Date start = new Date(f.parse(from).getTime());
			Date end = new Date(f.parse(to).getTime());

			String sql = "Select so.* From dbo.SoccerOrder so inner join dbo.TimeDivision td "
					+ " on so.timeId = td.id WHERE datediff(d, so.start_date, '" + start + "') <= 0 and datediff(d, so.start_date, '" + end + "') >=0";

			if (soccerFieldId != 0) {
				sql += " and td.soccerFieldId = " + soccerFieldId;
			}

			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				SoccerOrder so = new SoccerOrder();

				so.setId(rs.getInt("id"));
				so.setOrderNo(rs.getString("OrderNo"));
				so.setStatus(rs.getBoolean("status"));
				so.setStartDate(rs.getDate("start_date"));
				so.setEndDate(rs.getDate("end_date"));
				so.setTimeDivision(new SoccerFieldDao().getTimeDivisionById(rs.getInt("timeId")));
				so.setPerson(new PersonDao().getPersonById(rs.getInt("personId")));
				so.setCancel_date(rs.getDate("cancel_date"));

				lst.add(so);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e1) {
			e1.printStackTrace();
		} finally {
			closeConnection();
		}

		return lst;
	}

	public SoccerOrder getSoccerOrder(int id) {
		SoccerOrder so = null;
		String sql = "Select * From dbo.SoccerOrder where id = " + id;
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				so = new SoccerOrder();

				so.setId(rs.getInt("id"));
				so.setOrderNo(rs.getString("OrderNo"));
				so.setStatus(rs.getBoolean("status"));
				so.setStartDate(rs.getDate("start_date"));
				so.setEndDate(rs.getDate("end_date"));
				so.setTimeDivision(new SoccerFieldDao().getTimeDivisionById(rs.getInt("timeId")));
				so.setPerson(new PersonDao().getPersonById(rs.getInt("personId")));
				so.setCancel_date(rs.getDate("cancel_date"));
				so.setDeposits(rs.getFloat("deposits"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return so;
	}

	public List<SoccerOrder> getListSoccerOrder(Account acc) {
		List<SoccerOrder> listSoccerOrder = new ArrayList<SoccerOrder>();
		StringBuilder sql = new StringBuilder();
		sql.append("Select * from SoccerOrder");
		sql.append(" Where personId = " + acc.getPerson().getId());
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql.toString());
			while (rs.next()) {

				SoccerOrder so = new SoccerOrder();

				so.setId(rs.getInt("id"));
				so.setOrderNo(rs.getString("OrderNo"));
				so.setStatus(rs.getBoolean("status"));
				so.setStartDate(rs.getDate("start_date"));
				so.setEndDate(rs.getDate("end_date"));
				so.setTimeDivision(new TimeDivisionDao().getTimeDivisionById(rs.getInt("timeId")));
				so.setPerson(new PersonDao().getPersonById(rs.getInt("personId")));
				so.setCancel_date(rs.getDate("cancel_date"));
				so.setDeposits(rs.getFloat("deposits"));
				listSoccerOrder.add(so);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listSoccerOrder;
	}

	public boolean deleteSoccerOrder(int id) {
		// TODO Auto-generated method stub
		boolean isSuccess = false;
		String sql = "Update SoccerOrder SET [status] = 0, [cancel_date] = getdate() where id = " + id;
		try {
			int arr = stmt.executeUpdate(sql);
			if (arr > 0)
				isSuccess = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return isSuccess;
	}
}
